﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FOModels.News;
namespace DataAccess.News
{
    public class NewsDAL
    {
        private SQLDataAccess db;
        public NewsDAL()
        {
            db = new SQLDataAccess();
        }

        public DataTable getNewsByIDToGet(int NewsID, int langid)
        {
            return db.exeSelect("select pcl.Name, pc.[ProductCID], pl.ProductName,col.Name,ca.NewsID,ca.ProductID,ca.CompanyID,ca.ViewCount,ca.CreateDate,ca.CreateUser,ca.[Enable],ca.NewsImg,ca.NewsDeadline,cal.NewsTitle,cal.NewsSumary,ca.NewsHot,cal.NewsContent from News ca join NewsLang cal on ca.NewsID = cal.NewsID join Product p on ca.ProductID = p.ProductID join ProductLang pl on p.ProductID = pl.ProductID join Company co on ca.CompanyID = co.CompanyID join CompanyLang col on co.CompanyID = col.CompanyID join ProductCategory pc on p.ProductCID = pc.ProductCID join ProductCategoryLang pcl on pc.ProductCID = pcl.ProductCID where ca.Enable=1 and cal.LangID = " + langid + " and pl.LangID = " + langid + " and col.LangID= " + langid + " and pcl.LangID = " + langid + " and ca.NewsID = " + NewsID + "");
        }
        public DataTable getReferenceNews(int top, int ProductID, int NewsID, int langid)
        {
            return db.exeSelect("select TOP " + top + " pl.ProductName,col.Name,ca.NewsID,ca.ProductID,ca.CompanyID,ca.ViewCount,ca.CreateDate,ca.CreateUser,ca.[Enable],ca.NewsImg,ca.NewsDeadline,cal.NewsTitle,cal.NewsSumary,ca.NewsHot,cal.NewsContent from News ca join NewsLang cal on ca.NewsID = cal.NewsID join Product p on ca.ProductID = p.ProductID join ProductLang pl on p.ProductID = pl.ProductID join Company co on ca.CompanyID = co.CompanyID join CompanyLang col on co.CompanyID = col.CompanyID where ca.Enable=1 and cal.LangID = " + langid + " and pl.LangID = " + langid + " and col.LangID= " + langid + " and ca.ProductID = " + ProductID + " and ca.NewsID != " + NewsID + " order by ca.ProductID desc");
        }
        public DataTable getNewsByID(int NewsID, int langid)
        {
            return db.exeSelect("select * from News ca join NewsLang cal on ca.NewsID = cal.NewsID where ca.NewsID = " + NewsID + " and LangID = " + langid + "");
        }
        public DataTable getTOPNewsByNewsHot(int top, int langid)
        {
            return db.exeSelect("select TOP " + top + " * from News ca join NewsLang cal on ca.NewsID = cal.NewsID where ca.Enable=1 and ca.NewsHot = 1 and LangID = " + langid + " order by ca.NewsID desc");
        }
        public DataTable getTOPNewsByNewsHotAndProCate(int top, int ProductCID, int langid)
        {
            return db.exeSelect("select TOP " + top + " pcl.Name, pc.[ProductCID], pl.ProductName,col.Name,ca.NewsID,ca.ProductID,ca.CompanyID,ca.ViewCount,ca.CreateDate,ca.CreateUser,ca.[Enable],ca.NewsImg,ca.NewsDeadline,cal.NewsTitle,cal.NewsSumary,ca.NewsHot,cal.NewsContent from News ca join NewsLang cal on ca.NewsID = cal.NewsID join Product p on ca.ProductID = p.ProductID join ProductLang pl on p.ProductID = pl.ProductID join Company co on ca.CompanyID = co.CompanyID join CompanyLang col on co.CompanyID = col.CompanyID join ProductCategory pc on p.ProductCID = pc.ProductCID join ProductCategoryLang pcl on pc.ProductCID = pcl.ProductCID where ca.Enable=1 and cal.LangID = " + langid + " and pl.LangID = " + langid + " and col.LangID= " + langid + " and pcl.LangID = " + langid + " and pc.ProductCID = " + ProductCID + " and ca.NewsHot = 1 order by ca.NewsID desc");
        }
        public DataTable getTop5NewsOrderbyViewcount(int langid)
        {
            return db.exeSelect("select TOP 5 * from News ca join NewsLang cal on ca.NewsID = cal.NewsID where ca.Enable=1 and LangID = " + langid + " Order by ViewCount desc");
        }
        public DataTable getTop1NewsByID(int langid)
        {
            return db.exeSelect("select TOP 1 pcl.Name,pc.ProductCID, p.ProductID,pl.Name, ca.NewsID,ca.ProductID,ca.CompanyID,ca.ViewCount,ca.CreateDate,ca.CreateUser,ca.[Enable],ca.NewsImg,ca.NewsDeadline,cal.NewsTitle,cal.NewsSumary,ca.NewsHot,cal.NewsContent from ProductCategory pc join ProductCategoryLang pcl on pc.ProductCID = pcl.ProductCID join Product p on p.ProductCID = pc.ProductCID join ProductCategoryLang pl on p.ProductCID = pl.ProductCID join News ca on p.ProductID = ca.ProductID  join NewsLang cal on ca.NewsID = cal.NewsID where ca.Enable=1 and pl.LangID = " + langid + " and cal.LangID = " + langid + " and pcl.LangID = " + langid + " Order by ca.NewsID desc");
        }
        public DataTable getTop3NewsByNotID(int langid, int NewsID)
        {
            return db.exeSelect("select TOP 3 pcl.Name,pc.ProductCID, p.ProductID,pl.Name, ca.NewsID,ca.ProductID,ca.CompanyID,ca.ViewCount,ca.CreateDate,ca.CreateUser,ca.[Enable],ca.NewsImg,ca.NewsDeadline,cal.NewsTitle,cal.NewsSumary,ca.NewsHot,cal.NewsContent from ProductCategory pc join ProductCategoryLang pcl on pc.ProductCID = pcl.ProductCID join Product p on p.ProductCID = pc.ProductCID join ProductCategoryLang pl on p.ProductCID = pl.ProductCID join News ca on p.ProductID = ca.ProductID  join NewsLang cal on ca.NewsID = cal.NewsID where ca.Enable=1 and pl.LangID = " + langid + " and cal.LangID = " + langid + " and pcl.LangID = " + langid + " and ca.NewsID != " + NewsID + " Order by ca.NewsID desc");
        }
        public DataTable getNews(int langid)
        {
            return db.exeSelect("select pl.ProductName,col.Name,ca.NewsID,ca.ProductID,ca.CompanyID,ca.ViewCount,ca.CreateDate,ca.CreateUser,ca.[Enable],ca.NewsImg,ca.NewsDeadline,cal.NewsTitle,cal.NewsSumary,ca.NewsHot,cal.NewsContent from News ca join NewsLang cal on ca.NewsID = cal.NewsID join Product p on ca.ProductID = p.ProductID join ProductLang pl on p.ProductID = pl.ProductID join Company co on ca.CompanyID = co.CompanyID join CompanyLang col on co.CompanyID = col.CompanyID where ca.Enable=1 and cal.LangID = " + langid + " and pl.LangID = " + langid + " and col.LangID= " + langid + "");
        }
        public DataTable getNewsByProCate(int ProductCID, int langid)
        {
            return db.exeSelect("select pcl.Name, pc.[ProductCID], pl.ProductName,col.Name,ca.NewsID,ca.ProductID,ca.CompanyID,ca.ViewCount,ca.CreateDate,ca.CreateUser,ca.[Enable],ca.NewsImg,ca.NewsDeadline,cal.NewsTitle,cal.NewsSumary,ca.NewsHot,cal.NewsContent from News ca join NewsLang cal on ca.NewsID = cal.NewsID join Product p on ca.ProductID = p.ProductID join ProductLang pl on p.ProductID = pl.ProductID join Company co on ca.CompanyID = co.CompanyID join CompanyLang col on co.CompanyID = col.CompanyID join ProductCategory pc on p.ProductCID = pc.ProductCID join ProductCategoryLang pcl on pc.ProductCID = pcl.ProductCID where ca.Enable=1 and cal.LangID = " + langid + " and pl.LangID = " + langid + " and col.LangID= " + langid + " and pcl.LangID = " + langid + " and pc.ProductCID = " + ProductCID + "");
        }
        public DataTable getNewsByCate(int productcid)
        {
            return db.exeSelect("select * from News n join Product p on n.ProductID = p.ProductID where ProductCID = " + productcid + "");
        }
        public DataTable gettop1news()
        {
            return db.exeSelect("Select Top 1 NewsID from News order by NewsID desc");
        }
        public int UpdateNews(NewsModel models)
        {
            return db.exeUpdate("update News set ProductID = " + models.ProductID + ", CompanyID = " + models.CompanyID + " , Enable = '" + models.Enable + "' , NewsImg = '" + models.NewsImg + "', NewsDeadline = '" + models.NewsDeadline + "',UpdateDate=getdate(),UpdateUser ='" + models.UpdateUser + "',NewsHot = '" + models.NewsHot + "' where NewsID = " + models.NewsID + "");
        }
        public int UpdateNewsLang(NewsModel models, int langid)
        {
            return db.exeUpdate("update NewsLang set NewsTitle = N'" + models.NewsTitle + "', NewsSumary = N'" + models.NewsSumary + "',NewsContent=N'" + models.NewsContent + "' where NewsID = " + models.NewsID + " and LangID=" + langid + "");
        }
        public int InsertNews(NewsModel models)
        {
            return db.exeUpdate("insert into News(ProductID,CompanyID,CreateUser,NewsImg,NewsDeadline,NewsHot) values (" + models.ProductID + "," + models.CompanyID + ",N'" + models.CreateUser + "','" + models.NewsImg + "','" + models.NewsDeadline + "','" + models.NewsHot + "')");
        }
        public int DeleteNews(int NewsID)
        {
            return db.exeUpdate("Delete from News where NewsID = " + NewsID + "");
        }
        public DataTable getall(int langid)
        {
            return db.exeSelect("select n.NewsHot,n.NewsID,n.CompanyID,col.Name,p.ProductID,pl.ProductName,n.NewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.NewsTitle,la.FlagImage,n.ViewCount from News n join NewsLang nl on n.NewsID = nl.NewsID join Company co on n.CompanyID = co.CompanyID join CompanyLang col on co.CompanyID = col.CompanyID join Product p on n.ProductID = p.ProductID join ProductLang pl on p.ProductID = pl.ProductID join [Language] la on nl.LangID = la.ID   where nl.LangID = " + langid + " and la.ID = " + langid + " and pl.LangID=" + langid + " and col.LangID=" + langid + "");
        }
        public int InsertNewsLang(NewsModel models, int langid)
        {
            return db.exeUpdate("insert into NewsLang values (" + models.NewsID + "," + langid + ",N'" + models.NewsTitle + "',N'" + models.NewsSumary + "',N'" + models.NewsContent + "')");
        }
        public int DeleteNewsLang(int NewsID)
        {
            return db.exeUpdate("Delete from NewsLang where NewsID = " + NewsID + "");
        }
    }

}
